<!DOCTYPE html>
<html lang="en">
    <head prefix="og: https://ogp.me/ns#">
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <meta name="color-scheme" content="light dark">
  
  <title>day02-MySQL - Satellite</title>
  
    <link rel="shortcut icon" href="/favicon.png">
  
  
    <link rel='manifest' href='/manifest.json'>
  

  
  
  
  <meta property="og:title" content="day02-MySQL - Satellite" />
  
  <meta property="og:type" content="article" />
  
  <meta property="og:url" content="http://example.com/2023/09/21/day02-MySQL/index.html" />
  
  <meta property="og:image" content="/favicon.png" />
  
  <meta property="og:article:published_time" content="2023-09-21T02:07:50.000Z" />
  
  <meta property="og:article:author" content="John Doe" />
  
  

  
<link rel="stylesheet" href="/css/var.css">

  
<link rel="stylesheet" href="/css/main.css">

  
<link rel="stylesheet" href="/css/typography.css">

  
<link rel="stylesheet" href="/css/code-highlighting.css">

  
<link rel="stylesheet" href="/css/components.css">

  
<link rel="stylesheet" href="/css/nav.css">

  
<link rel="stylesheet" href="/css/paginator.css">

  
<link rel="stylesheet" href="/css/footer.css">

  
<link rel="stylesheet" href="/css/post-list.css">

  
  
<link rel="stylesheet" href="/css/rainbow-banner.css">

  
  
  
<link rel="stylesheet" href="/css/toc.css">

  
  
  
  
  
<link rel="stylesheet" href="/css/post.css">

  
  
  
  
  

  
<meta name="generator" content="Hexo 6.3.0"></head>
    <body
        data-color-scheme="auto"
        data-uppercase-categories="true"
        
        data-rainbow-banner="true"
        data-rainbow-banner-shown="auto"
        data-rainbow-banner-month="6"
        data-rainbow-banner-colors="#e50000,#ff8d00,#ffee00,#008121,#004cff,#760188"
        
        data-config-root="/"
        
        data-toc="true"
        data-toc-max-depth="2"
        
        
    >
        <nav id="theme-nav">
    <div class="inner">
        <a class="title" href="/">Satellite&#39;s Blog</a>
        <div class="nav-arrow"></div>
        <div class="nav-items">
            <a class="nav-item nav-item-home" href="/">Home</a>
            
            
            <a class="nav-item" href="/archives">Archives</a>
            
            
            
            <a class="nav-item" href="/about">About</a>
            
            
            
            <a class="nav-item nav-item-github nav-item-icon" href="https://github.com/Satellite2426" target="_blank" aria-label="GitHub">&nbsp;</a>
            
            
        </div>
    </div>
</nav>
        
<article class="post">
    <div class="meta">
        

        
        <div class="date" id="date">
            <span>September</span>
            <span>21,</span>
            <span>2023</span>
        </div>
        

        <h2 class="title">day02-MySQL</h2>
    </div>

    <div class="divider"></div>

    <div class="content">
        <h1 id="1-约束"><a href="#1-约束" class="headerlink" title="1. 约束"></a>1. 约束</h1><h2 id="1-1-约束的概念"><a href="#1-1-约束的概念" class="headerlink" title="1.1 约束的概念"></a>1.1 约束的概念</h2><ul>
<li>约束是作用于表中列上的规则，用于限制加入表的数据</li>
<li>约束的存在保证了数据库中数据的正确性、有效性和完整性</li>
</ul>
<h2 id="1-2-约束的分类"><a href="#1-2-约束的分类" class="headerlink" title="1.2 约束的分类"></a>1.2 约束的分类</h2><table>
<thead>
<tr>
<th>约束名称</th>
<th>描述</th>
<th>关键字</th>
</tr>
</thead>
<tbody><tr>
<td>非空约束</td>
<td>保证列中所有数据不能有null值</td>
<td>NOT NULL</td>
</tr>
<tr>
<td>唯一约束</td>
<td>保证列中所有数据各不相同</td>
<td>UNIQUE</td>
</tr>
<tr>
<td>主键约束</td>
<td>主键是一行数据的唯一标识，要求非空且唯一</td>
<td>PRIMARY KEY</td>
</tr>
<tr>
<td>检查约束</td>
<td>保证列中的值满足某一条件</td>
<td>CHECK</td>
</tr>
<tr>
<td>默认约束</td>
<td>保存数据时，未指定值则采用默认值</td>
<td>DEFAULT</td>
</tr>
<tr>
<td>外键约束</td>
<td>外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性</td>
<td>FOREIGN KEY</td>
</tr>
</tbody></table>
<blockquote>
<p>注意：MySQL不支持检查约束</p>
</blockquote>
<p><strong>自动增长</strong>：<code>auto_increment</code> 当列是数字类型并且 唯一约束</p>
<h2 id="1-3-外键约束"><a href="#1-3-外键约束" class="headerlink" title="1.3 外键约束"></a>1.3 外键约束</h2><p>概念：外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性</p>
<p>语法：</p>
<ul>
<li>添加约束</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 创建表时添加外键约束</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 表名 (</span><br><span class="line">	列名 数据类型,</span><br><span class="line">	...</span><br><span class="line">	<span class="keyword">constraint</span> 外键名称 <span class="keyword">foreign</span> key (外键列名) <span class="keyword">references</span> 主表(主表列名)</span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 建完表后添加外键约束</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">add</span> <span class="keyword">constraint</span> 外键名称 <span class="keyword">foreign</span> key (外键字段名称) <span class="keyword">references</span> 主表名称(主表列名称);</span><br></pre></td></tr></table></figure>

<ul>
<li>删除约束</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">drop</span> <span class="keyword">foreign</span> key 外键名称;</span><br></pre></td></tr></table></figure>

<h1 id="2-数据库设计"><a href="#2-数据库设计" class="headerlink" title="2. 数据库设计"></a>2. 数据库设计</h1><h2 id="2-1-软件的研发步骤"><a href="#2-1-软件的研发步骤" class="headerlink" title="2.1 软件的研发步骤"></a>2.1 软件的研发步骤</h2><img src="https://csh-picture.oss-cn-shenzhen.aliyuncs.com/img202309032026736.png" alt="image-20230903202556661" style="zoom:80%;" />

<h2 id="2-2-数据库设计概念"><a href="#2-2-数据库设计概念" class="headerlink" title="2.2 数据库设计概念"></a>2.2 数据库设计概念</h2><ul>
<li>数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型</li>
<li>建立数据库中的<strong>表结构</strong>以及<strong>表与表之间的关联关系</strong>的过程</li>
<li>有哪些表？表里有哪些字段？表和表之间有什么关系？</li>
</ul>
<h2 id="2-3-数据库设计的步骤"><a href="#2-3-数据库设计的步骤" class="headerlink" title="2.3 数据库设计的步骤"></a>2.3 数据库设计的步骤</h2><ol>
<li>需求分析（数据是什么?数据具有哪些属性?数据与属性的特点是什么）</li>
<li>逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）</li>
<li>物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）</li>
<li>维护设计（1.对新的需求进行建表;2.表优化）</li>
</ol>
<h3 id="2-3-1-表关系"><a href="#2-3-1-表关系" class="headerlink" title="2.3.1 表关系"></a>2.3.1 表关系</h3><p><strong>1.一对一</strong></p>
<p>如：用户  和  用户详情</p>
<p>​		  一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能</p>
<p> 实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为&#x3D;&#x3D;唯一(UNIQUE)&#x3D;&#x3D;</p>
<p><strong>2.一对多 &#x2F; 多对一</strong></p>
<p>如：部门  和  员工</p>
<p>​		  一个部门对应多个员工，一个员工对应一个部门</p>
<p>实现方式：在&#x3D;&#x3D;多&#x3D;&#x3D;的一方建立&#x3D;&#x3D;外键&#x3D;&#x3D;，指向一的一方的主键</p>
<p><strong>3.多对多</strong></p>
<p>如：商品  和  订单</p>
<p>​		  一个商品对应多个订单，一个订单包含多个商品</p>
<p>实现方式：建立第三张&#x3D;&#x3D;中间表&#x3D;&#x3D;，中间表至少包含&#x3D;&#x3D;两个外键&#x3D;&#x3D;，分别&#x3D;&#x3D;关联两方主键&#x3D;&#x3D;</p>
<h1 id="3-多表查询"><a href="#3-多表查询" class="headerlink" title="3. 多表查询"></a>3. 多表查询</h1><h2 id="3-1-连接查询"><a href="#3-1-连接查询" class="headerlink" title="3.1 连接查询"></a>3.1 连接查询</h2><h3 id="3-1-1-内连接"><a href="#3-1-1-内连接" class="headerlink" title="3.1.1 内连接"></a>3.1.1 内连接</h3><p><strong>定义</strong>：相当于查询A 、B交集数据</p>
<p><strong>语法</strong>：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 隐式内连接</span></span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span>, 表<span class="number">2.</span>.. <span class="keyword">where</span> 条件;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 显示外连接</span></span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span> <span class="keyword">inner</span>[可省略] <span class="keyword">join</span> 表<span class="number">2</span> <span class="keyword">on</span> 条件;</span><br></pre></td></tr></table></figure>

<h3 id="3-1-2-外连接"><a href="#3-1-2-外连接" class="headerlink" title="3.1.2 外连接"></a>3.1.2 外连接</h3><ul>
<li><p>左外连接：相当于查询A表所有数据和交集部分数据</p>
</li>
<li><p>右外连接：相当于查询B表所有数据和交集部分数据</p>
</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 左外连接</span></span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span> <span class="keyword">left</span> <span class="keyword">outer</span>[可省略] <span class="keyword">join</span> 表<span class="number">2</span> <span class="keyword">on</span> 条件;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 右外连接</span></span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span> <span class="keyword">right</span> <span class="keyword">outer</span>[可省略] <span class="keyword">join</span> 表<span class="number">2</span> <span class="keyword">on</span> 条件; </span><br></pre></td></tr></table></figure>

<img src="https://csh-picture.oss-cn-shenzhen.aliyuncs.com/img202309032110655.png" alt="image-20230903211039627" style="zoom:80%;" />

<h2 id="3-2-子查询"><a href="#3-2-子查询" class="headerlink" title="3.2 子查询"></a>3.2 子查询</h2><p><strong>定义</strong>：查询中嵌套查询，称嵌套查询为子查询</p>
<p>子查询根据查询结果不同，作用不同：</p>
<ul>
<li>单行单列：作为条件值，使用 <strong>&#x3D;、!&#x3D;、&gt;、&lt;</strong> 等进行条件判断</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表 <span class="keyword">where</span> 字段名<span class="operator">=</span>(子查询);</span><br></pre></td></tr></table></figure>

<ul>
<li>多行单列：作为条件值，使用 <strong>in</strong> 等关键字进行条件判断</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表 <span class="keyword">where</span> 字段名 <span class="keyword">in</span> (子查询);</span><br></pre></td></tr></table></figure>

<ul>
<li>多行多列：作为虚拟表</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> (子查询) <span class="keyword">where</span> 条件;</span><br></pre></td></tr></table></figure>

<h1 id="4-事务"><a href="#4-事务" class="headerlink" title="4. 事务"></a>4. 事务</h1><h2 id="4-1-事务简介"><a href="#4-1-事务简介" class="headerlink" title="4.1 事务简介"></a>4.1 事务简介</h2><ul>
<li>数据库的&#x3D;&#x3D;事务&#x3D;&#x3D;(Transaction)是一种机制、一个操作序列，包含了&#x3D;&#x3D;一组数据库操作命令&#x3D;&#x3D; </li>
<li>事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令&#x3D;&#x3D;要么同时成功，要么同时失败&#x3D;&#x3D; </li>
<li>事务是一个不可分的工作逻辑单元</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 开启事务</span></span><br><span class="line"><span class="keyword">start</span> transaction;</span><br><span class="line">或者 <span class="keyword">begin</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 提交事务</span></span><br><span class="line"><span class="keyword">commit</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 回滚事务</span></span><br><span class="line"><span class="keyword">rollback</span>;</span><br></pre></td></tr></table></figure>

<h2 id="4-2-事务四大特征"><a href="#4-2-事务四大特征" class="headerlink" title="4.2 事务四大特征"></a>4.2 事务四大特征</h2><p><strong>ACID</strong></p>
<ul>
<li>原子性(Atomicity)：事务是不可分割的最小操作单位，要么同时成功，要么同时失败</li>
<li>一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态</li>
<li>隔离性 (Isolation)：多个事务之间，操作的可见性</li>
<li>持久性 (Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的</li>
</ul>
<blockquote>
<p>MySQL的事务是自动提交的</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查看事务的默认提交方式</span></span><br><span class="line"><span class="keyword">select</span> @<span class="variable">@autocommit</span>;</span><br><span class="line"><span class="comment">-- 1 自动提交	0 手动提交</span></span><br><span class="line"><span class="comment">-- 修改事务提交方式</span></span><br><span class="line"><span class="keyword">set</span> @<span class="variable">@autocommit</span> <span class="operator">=</span> <span class="number">0</span>;</span><br></pre></td></tr></table></figure>
</blockquote>

    </div>

    
    <div class="about">
        <h1>About this Post</h1>
        <div class="details">
            <p>This post is written by John Doe, licensed under <a target="_blank" rel="noopener" href="https://creativecommons.org/licenses/by-nc/4.0">CC BY-NC 4.0</a>.</p>
        </div>
        
        <p class="tags text-capitalize">
            
            <i class="icon"></i>
            <a href="/tags/Java-Web/" class="tag">#Java Web</a>
        </p>
        
    </div>
    

    <div class="container post-prev-next">
        
        <a href="/2023/09/21/day03-JDBC/" class="next">
            <div>
                <div class="text">
                    <p class="label">Next</p>
                    <h3 class="title">day03-JDBC</h3>
                </div>
            </div>
        </a>
        
        
        <a href="/2023/09/21/day01-MySQL%E5%9F%BA%E7%A1%80/" class="prev">
            <div>
                <div class="text">
                    <p class="label">Previous</p>
                    <h3 class="title">day01-MySQL基础</>
                </div>
            </div>
        </a>
        
    </div>

    
        
        
    
</article>

        <footer>
    <div class="inner">
        <div class="links">
            
            <div class="group">
                <h2 class="title">Blog</h2>
                
                <a href="/" class="item">Blog</a>
                
                <a href="/archives" class="item">Archives</a>
                
                <a href="/tags" class="item">Tags</a>
                
                <a href="/categories" class="item">Categories</a>
                
                <a href="/about" class="item">About</a>
                
            </div>
            
            <div class="group">
                <h2 class="title">Projects</h2>
                
                <a target="_blank" rel="noopener" href="https://github.com/MrWillCom/rsa-cli" class="item">RSA CLI</a>
                
                <a target="_blank" rel="noopener" href="https://github.com/MrWillCom/hexo-theme-cupertino" class="item">Hexo Theme Cupertino</a>
                
                <a target="_blank" rel="noopener" href="https://github.com/MrWillCom/a-calendar" class="item">A Calendar</a>
                
                <a target="_blank" rel="noopener" href="https://github.com/MrWillCom/auto-mirroring-bucket" class="item">Auto Mirroring Bucket</a>
                
            </div>
            
            <div class="group">
                <h2 class="title">Me</h2>
                
                <a target="_blank" rel="noopener" href="https://github.com/Satellite2426" class="item">GitHub</a>
                
                <a href="mailto:chaoshihan2426@gmail.com" class="item">Email</a>
                
            </div>
            
        </div>
        <span>&copy; 2023 John Doe<br>Powered by <a href="http://hexo.io/" target="_blank">Hexo</a> </span>
        
        
            <br>
            <div class="color-scheme-toggle" role="radiogroup" id="theme-color-scheme-toggle">
                <label>
                    <input type="radio" value="light">
                    <span>Light</span>
                </label>
                <label>
                    <input type="radio" value="dark">
                    <span>Dark</span>
                </label>
                <label>
                    <input type="radio" value="auto">
                    <span>Auto</span>
                </label>
            </div>
        
    </div>
</footer>


        
<script src="/js/main.js"></script>

        
        
        

        
        <script src="https://unpkg.com/scrollreveal"></script>
        <script>
            window.addEventListener('load', () => {
                ScrollReveal({ delay: 250, reset: true, easing: 'cubic-bezier(0, 0, 0, 1)' })
                ScrollReveal().reveal('.post-list-item .cover-img img')
                ScrollReveal().reveal('.post-list-item, .card, .content p img, .content .block-large img', { distance: '60px', origin: 'bottom', duration: 800 })
            })
        </script>
        
    </body>
</html>